namespace CISLib
{
    using CISLib.EncryptClasses;
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    public class DBAccess : IDisposable
    {
        private string _ConnectString = string.Empty;
        private SqlCommand _MyCom;
        private SqlConnection _MyCon;
        //private DataSet _MyDataSet;
        private SqlDataReader _MyDateReader;
        private SqlTransaction _MyTransaction;
        private string _strSQL;

        public DBAccess()
        {
            DESEncrypt mydes = new DESEncrypt();
            //this._MyCon = new SqlConnection(mydes.Decrypt(ConfigurationSettings.AppSettings["ConnectString"], "ASB12345"));
            this._MyCon = new SqlConnection(ConfigurationSettings.AppSettings["ConnectString"]);
            this._MyCom = new SqlCommand("", this._MyCon);
        }

        public void AddParameters(object DBvalue, string field, SqlDbType dbType)
        {
            SqlParameter Prm = new SqlParameter("@" + field, dbType);
            Prm.Value = DBvalue;
            this._MyCom.Parameters.Add(Prm);
        }

        public void ClearParameters()
        {
            this._MyCom.Parameters.Clear();
        }

        public void DBClose()
        {
            this._MyCon.Close();
        }

        public void DBExcuteNonQuery()
        {
            this._MyCom.ExecuteNonQuery();
        }

        public void DBExcuteReader()
        {
            this._MyDateReader = this._MyCom.ExecuteReader(CommandBehavior.CloseConnection);
        }

        public object DBExecuteScalar()
        {
            return this._MyCom.ExecuteScalar();
        }

        public void DBOpen()
        {
            this._MyCon.Open();
        }

        public DataSet DBReturnDataSet()
        {
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter(this._MyCom);
            DataSet MyDataSet = new DataSet();
            MyDataAdapter.Fill(MyDataSet);
            return MyDataSet;
        }

        public void DBSetPro(string sql)
        {
            this._MyCom.CommandType = CommandType.StoredProcedure;
            this._MyCom.CommandText = sql;
        }

        public void DBSetSelect(string sql)
        {
            this._MyCom.CommandType = CommandType.Text;
            this._MyCom.CommandText = sql;
        }

        public void DBTransactionBegin()
        {
            this._MyTransaction = this._MyCon.BeginTransaction();
            this._MyCom.Connection = this._MyCon;
            this._MyCom.Transaction = this._MyTransaction;
        }

        public void DBTransactionCommit()
        {
            this._MyTransaction.Commit();
        }

        public void DBTransactionRollback()
        {
            this._MyTransaction.Rollback();
        }

        public SqlDataReader DBReader
        {
            get
            {
                return this._MyDateReader;
            }
        }

        public string SQL
        {
            get
            {
                return this._strSQL;
            }
            set
            {
                this._strSQL = value;
            }
        }

        public void Dispose()
        {
            if (this._MyCon != null)
            {
                this._MyCon.Dispose();
                this._MyCon = null;
            }
        }
    }
}

